use sqlx::{Row, Column, sqlite::SqlitePool};
use super::{database::{DataBase, MyRow, ExecuteResult}, value_helper::sqlite2lua};
use super::env::TOKIO;
use mlua::UserData;
use anyhow::Result;

impl MyRow for sqlx::sqlite::SqliteRow {}

pub(crate) struct Sqlite{
    pool: Option<SqlitePool>,
}

impl Default for Sqlite{
    fn default() -> Self {
        Self { pool: None }
    }
}

impl DataBase for Sqlite {
    type Row = sqlx::sqlite::SqliteRow;

    async fn connect(
        &mut self, 
        _host: Option<String>, 
        _port: Option<u16>, 
        _user: Option<String>, 
        _password: Option<String>,
         database: String,
        _timeout: Option<u64>
        ) -> Result<()> {
        let url = match database.as_str() {
            ":memory" => ":memory".to_string(),
            other => match std::fs::metadata(other) {
                Ok(_) => other.into(),
                Err(_) => match std::fs::File::create(other) {
                    Ok(_) => other.into(),
                    Err(e) => return Err(anyhow::anyhow!("Failed to create database file: {}", e)),
                }
            }
        };
        let connection_string = format!("sqlite://{}", url);
        self.pool = Some(SqlitePool::connect(&connection_string).await?);
        Ok(())
    }

    async fn execute(&self, query: String) -> anyhow::Result<ExecuteResult> {
        if let Some(pool) = &self.pool {
            let res = sqlx::query(&query).execute(pool).await?;
            Ok(ExecuteResult { 
                rows_affected: res.rows_affected(), 
                last_insert_id: Some(res.last_insert_rowid() as u64) 
            })
        }else{
            Err(anyhow::anyhow!("Database not connected"))
        }
    }

    async fn query(&self, query: String) -> anyhow::Result<Vec<Self::Row>> {
        if let Some(pool) = &self.pool {
            let rows = sqlx::query(&query).fetch_all(pool).await?;
            Ok(rows)
        } else {
            Err(anyhow::anyhow!("Database not connected"))
        }
    }
} 

impl UserData for Sqlite {
    fn add_methods<M: mlua::UserDataMethods<Self>>(methods: &mut M) {
        methods.add_async_method_mut("connect", |_lua, mut this, database: String| async move{
            let _guard= TOKIO.enter();
            this.connect(None, None, None, None, database, None).await.map_err(|e| mlua::Error::external(e))?;
            Ok(())
        });

        methods.add_async_method("execute", |lua, this, sql: String| async move {
            let _guard= TOKIO.enter();
            let res = this.execute(sql).await.map_err(|e| mlua::Error::external(e))?;
            let t = lua.create_table()?;
            t.set("rows_affected", res.rows_affected)?;
            t.set("last_insert_id", res.last_insert_id)?;
            Ok(t)

        });

        methods.add_async_method("query", |lua, this, sql: String| async move{
            let _guard= TOKIO.enter();
            let rows = this.query(sql).await.map_err(|e| mlua::Error::external(e))?;
            let lua_rows = lua.create_table()?;
            for (index, row) in rows.iter().enumerate() {
                let lua_row = lua.create_table()?;
                for (i, col) in row.columns().iter().enumerate() {
                    let value = sqlite2lua(&lua, row, i, col.type_info())
                        .map_err(|e| mlua::Error::external(e))?;
                    lua_row.set(col.name(), value)?;
                }
                lua_rows.set(index+1, lua_row)?;
            }
            Ok(lua_rows)
        });
    }
}